package cn.milk.dao;

import cn.milk.model.User;
import cn.milk.util.DBUtil;
import org.json.JSONArray;
import org.json.JSONObject;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDAO {
    public User findUserByUsernameAndPassword(String username, String password) {
        String sql = "SELECT * FROM user WHERE username = ? AND password = ?";
        try (Connection connection = DBUtil.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, username);
            ps.setString(2, password);
            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
                User user = new User();
                user.setUserId(rs.getInt("user_id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setRole(rs.getInt("role"));
                return user;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<String> getUsers() throws SQLException {
        List<String> users = new ArrayList<>();
        String sql = "SELECT user_id, username FROM user";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                users.add(rs.getString("username"));
            }
        }
        return users;
    }

    public List<User> getUserList() throws SQLException {
        List<User> users = new ArrayList<>();
        String sql = "SELECT user_id, username, role FROM user"; // 假设需要用户的角色信息
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setUserId(rs.getInt("user_id"));
                user.setUsername(rs.getString("username"));
                user.setRole(rs.getInt("role")); // 假设有角色字段
                users.add(user);
            }
        }
        return users;
    }


    public String getUsernameById(int userId) throws SQLException {
        String sql = "SELECT username FROM user WHERE user_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getString("username");
            }
        }
        return null;
    }

    public boolean saveUser(User user) {
        String sql = "INSERT INTO user (username, password, role) VALUES (?, ?, ?)";
        try (Connection connection = DBUtil.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getPassword());
            ps.setInt(3, user.getRole());
            int rows = ps.executeUpdate();
            return rows > 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean isUsernameTaken(String username) {
        String sql = "SELECT 1 FROM user WHERE username = ?";
        try (Connection connection = DBUtil.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, username);
            ResultSet rs = ps.executeQuery();
            return rs.next();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    private static final int PAGE_SIZE = 5;

    // 获取用户列表（分页）
    public static JSONObject getUserList(int page, String keyword) throws SQLException {
        String query = "SELECT * FROM user WHERE username LIKE ? LIMIT ? OFFSET ?";
        String countQuery = "SELECT COUNT(*) FROM user WHERE username LIKE ?";
        int offset = (page - 1) * PAGE_SIZE;

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(query);
             PreparedStatement countStmt = conn.prepareStatement(countQuery)) {

            String searchKeyword = "%" + (keyword == null ? "" : keyword) + "%";

            // 设置查询参数
            stmt.setString(1, searchKeyword);
            stmt.setInt(2, PAGE_SIZE);
            stmt.setInt(3, offset);

            countStmt.setString(1, searchKeyword);

            // 查询用户数据
            ResultSet rs = stmt.executeQuery();
            JSONArray users = new JSONArray();
            while (rs.next()) {
                JSONObject user = new JSONObject();
                user.put("userId", rs.getInt("user_id"));
                user.put("username", rs.getString("username"));
                user.put("role", rs.getInt("role"));
                users.put(user);
            }

            // 查询总记录数
            ResultSet countRs = countStmt.executeQuery();
            countRs.next();
            int totalRecords = countRs.getInt(1);
            int totalPages = (int) Math.ceil((double) totalRecords / PAGE_SIZE);

            // 返回数据
            JSONObject result = new JSONObject();
            result.put("users", users);
            result.put("totalPages", totalPages);
            return result;
        }
    }


    // 添加用户
    public static boolean addUser(String username, String password, int role) throws SQLException {
        String query = "INSERT INTO user (username, password, role) VALUES (?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(query)) {
            stmt.setString(1, username);
            stmt.setString(2, password);
            stmt.setInt(3, role);
            return stmt.executeUpdate() > 0;
        }
    }

    // 删除用户
    public static boolean deleteUser(int id) throws SQLException {
        String query = "DELETE FROM user WHERE user_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(query)) {
            stmt.setInt(1, id);
            return stmt.executeUpdate() > 0;
        }
    }

    // 更新用户
    public static boolean updateUser(int id, String username) throws SQLException {
        String query = "UPDATE user SET username = ? WHERE user_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(query)) {
            stmt.setString(1, username);
            stmt.setInt(2, id);
            return stmt.executeUpdate() > 0;
        }
    }
}
